<?php

namespace App\Services;


use App\Jobs\Test;
use App\Models\ApprovalBorrow;
use App\Models\BaseModel;
use App\Models\PartCompany;
use App\Models\Project;
use App\Models\ProjectClassify;
use App\Models\SortLink;
use App\Models\SortLinkSearch;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 案卷目录服务层类
 * Class BookTitleService
 * @package App\Services
 */
class SearchStageService extends BaseService
{
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * 获取借阅记录
     */
    public function getBorrowLogs($perPage, $search)
    {
        $borrowUnit   = $search['borrow_unit'];
        $borrowUser   = $search['borrow_user'];
        $borrowMobile = $search['borrow_mobile'];
        $list         = ApprovalBorrow::query()
            ->when($borrowUnit, function ($query) use ($borrowUnit) {
                return $query->where('borrow_unit', 'like', '%' . $borrowUnit . '%');
            })
            ->when($borrowUser, function ($query) use ($borrowUser) {
                return $query->where('borrow_unit', 'like', '%' . $borrowUser . '%');
            })
            ->when($borrowMobile, function ($query) use ($borrowMobile) {
                return $query->where('borrow_mobile', '=', $borrowMobile);
            })
            ->where('creator_id', user_id())
            ->paginate($perPage);

        if ($list->isEmpty()) return [];

        $list = $list->toArray();

        foreach ($list['data'] as &$val) {
            $val['borrow_user_info'] = $val['borrow_user'] . ' ' . $val['borrow_mobile'];
            $val['is_online_text']   = $val['is_online'] == 1 ? '线上' : '线下';
            $val['borrow_type']      = $val['borrow_type'] == 1 ? '按卷借阅' : '按件借阅';
            $val['expire_time']      = Carbon::parse($val['expire_time'])->diffForHumans();
        }

        return $this->removeNoUsedPageParams($list);
    }

    /**
     * 获取当前用户能相关的项目分类
     *
     * @param $projectId
     * @return array
     * @author: lxk
     * @date: 2024/3/4 14:02
     */
    public function getProjectClassify($projectId): array
    {
        $ids            = [];
        $tables         = [];
        $partCompanyIds = ApprovalService::getPartCompanyIds($projectId);
        foreach ($partCompanyIds as $partCompanyId) {
            $projectClassifyIds = ProjectClassifyService::getProjectClassifyIds($partCompanyId);
            foreach ($projectClassifyIds as $projectClassifyId) {
                $ids[] = $projectClassifyId; //必须放这里，别乱动
                $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table)) {
                    $tables[] = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                }
            }
        }
        $list = ProjectClassify::query()
            ->whereIn('id', array_unique($ids))
            ->select('id', 'title', 'archives_no', 'parent_id', 'classify_id')
            ->get()
            ->toArray();
        $project = Project::query()->find($projectId);
        foreach ($list as &$val) {
            $val['data_count'] = 0;
            if (str_contains($val['archives_no'], '[项目代码]')) {
                $val['archives_no'] = str_replace('[项目代码]', $project->document_number, $val['archives_no']);
            }
            foreach ($tables as $table) {
                $tableParam = CommonService::getTableParams($table);
                if ($tableParam['id2'] == $val['id']) { //->where('status', 2)
                    $val['data_count'] += DB::table($table)->whereNull('deleted_at')->count(); //这里要用累加，因为同一个单位工程可能有多个参建单位做
                }
            }
        }
        $list2    = $this->getProjectClassifyData($projectId, $partCompanyIds, $list, 0);
        $data     = [];
        $redisKey = "tmpPt:{$projectId}";
        foreach ($list2 as $val2) {
            if (redis()->sadd($redisKey, $val2['title'] . $val2['archives_no'])) { //会出现业主、监理、施工 三个【施工竣工阶段】，这里排除下
                $data[] = $val2;
            }
        }
        redis()->del($redisKey);
        $info = SortLinkSearch::query()->where('project_id', $projectId)->first();
        if (empty($info)) {
            $this->saveUnitProjectSort($projectId, $data);
        }

        return $data;
    }

    /**
     * 把单位工程树状图按照顺序写进表里面
     *
     * @param $projectId
     * @param $data
     * @author: lxk
     * @date: 2024/5/23 10:10
     */
    public function saveUnitProjectSort($projectId, $data)
    {
        $projectClassifyIds = CommonService::getRecursionData($data);
        $insertData         = [];
        $projectClassifyIds = array_unique($projectClassifyIds);
        sort($projectClassifyIds);
        foreach ($projectClassifyIds as $projectClassifyId) {
            $insertData[] = [
                'project_id'          => $projectId,
                'project_classify_id' => $projectClassifyId,
            ];
        }
        SortLinkSearch::query()->insert($insertData);
    }

    /**
     * 获取单位工程树，这里跟立卷阶段那个还是区分开吧 多了太乱
     *
     * @param $list
     * @param $id
     * @return array
     * @author: lxk
     * @date: 2025-01-06 14:29
     */
    public function getProjectClassifyData($projectId, $partCompanyIds, $list, $id): array
    {
        $tree = [];
        foreach ($list as $value) {
            $value['have_children'] = 0;
            if ($value['parent_id'] == $id) {
                $value['children'] = self::getProjectClassifyData($projectId, $partCompanyIds, $list, $value['classify_id']);
                if ($value['children']) {
                    $children = $value['children'];
                    foreach ($partCompanyIds as $partCompanyId) {
                        $redisKey  = "stChildrenBts:{$projectId}:{$value['id']}"; //检索阶段的
                        $selfTable = "book_titles_{$partCompanyId}_{$value['id']}";
                        redis()->sadd($redisKey, $selfTable); //存本身
                        foreach ($children as $value2) {
                            $childTable = "book_titles_{$partCompanyId}_{$value2['id']}";
                            redis()->sadd($redisKey, $childTable); //存本身
                            if ($value2['have_children']) {
                                foreach ($value2['children'] as $value3) {
                                    $childTable = "book_titles_{$partCompanyId}_{$value3['id']}";
                                    redis()->sadd($redisKey, $childTable); //存本身
                                    if ($value3['have_children']) {
                                        foreach ($value3['children'] as $value4) {
                                            $childTable = "book_titles_{$partCompanyId}_{$value4['id']}";
                                            redis()->sadd($redisKey, $childTable); //存本身
                                            if ($value4['have_children']) {
                                                foreach ($value4['have_children'] as $value5) {
                                                    $childTable = "book_titles_{$partCompanyId}_{$value5['id']}";
                                                    redis()->sadd($redisKey, $childTable); //存本身
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    $value['have_children'] = 1; //下面，因为有的单位工程本身有值（原则上本身有下级的单位工程不要传案卷，但是个别还是传了），所以也要加上
                    $value['data_count']    = $value['data_count'] + array_sum(array_column($value['children'], 'data_count'));
                } else {
                    unset($value['children']);
                }
                $tree[] = $value;
            }
        }

        return $tree;
    }

    /**
     * 【检索阶段】按照【参见单位】获取案卷目录
     */
    public function getPartCompanyBookTitles($partCompanyId, $unitProjectName, $titleKeyword, $archivesNo, $perPage, $status = 2)
    {
        if ($unitProjectName) {
            $projectClassifyIds = ProjectClassify::query()->where('title', 'like', "%{$unitProjectName}%")->pluck('id');
            if ($projectClassifyIds->isEmpty()) return [];
            $projectClassifyIds = $projectClassifyIds->toArray();
        } else {
            $projectClassifyIds = ProjectClassifyService::getProjectClassifyIds($partCompanyId);
        }
        $tables = [];
        foreach ($projectClassifyIds as $projectClassifyId) {
            $table1 = "book_titles_{$partCompanyId}_{$projectClassifyId}";
            $table2 = "book_catalogs_{$partCompanyId}_{$projectClassifyId}";
            if (Schema::hasTable($table1) && Schema::hasTable($table2)) {
                if (DB::table($table1)->whereNull('deleted_at')->first()) {
                    $tables[] = $table1;
                }
            }
        }

        return $this->getUnionBookTitleList($tables, $perPage, $titleKeyword, $archivesNo, null, $status);
    }

    /**
     * 获取多个数据表的案卷数据
     */
    public function getUnionBookTitleList2($projectId, $tables, $perPage, $titleKeyword, $archivesNo = null, $status = 0)
    {
        $queries        = collect(); //定义一个集合
        $totalPageNum   = 0;
        $totalBlueprint = 0;
        $tables         = array_unique($tables);
        foreach ($tables as $table) {
            if (Schema::hasTable($table)) {
                $tableParam        = CommonService::getTableParams($table);
                $partCompanyId     = $tableParam['id1'];
                $projectClassifyId = $tableParam['id2'];
                $catalogTable      = 'book_catalogs_' . $partCompanyId . '_' . $projectClassifyId;
                /**
                 * 构造一个基础查询语句
                 */
                $sumBaseQuery = DB::table($table)
                    ->whereNull('deleted_at')
                    ->when($titleKeyword, function ($query) use ($table, $titleKeyword, $catalogTable) {
                        $likeSearch = "%{$titleKeyword}%";
                        $query->where('title', 'like', $likeSearch)
                            ->orWhereIn("$table.id", function (Builder $query) use ($catalogTable, $likeSearch) {
                                $query->select('book_title_id')->from($catalogTable)->where("$catalogTable.title", 'like', $likeSearch);
                            });
                    })
                    ->when($archivesNo, function ($query) use ($archivesNo, $table) {
                        $likeSearch2 = "%{$archivesNo}%";
                        $query->where("$table.archives_no", 'like', $likeSearch2);
                    });
                /**
                 * 计算总页次
                 */
                $totalPageNum += $sumBaseQuery->sum('page_num');
                /**
                 * 计算总蓝图数
                 */
                $totalBlueprint += $sumBaseQuery->sum('blueprint');
                /**
                 * 获取数据列表
                 */
                $queries->push( //分表查询 已通过
                    DB::table($table)->from("$table as bt")
                        ->leftJoin('project_classifies as pc1', 'pc1.id', '=', 'bt.project_classify_id')
                        ->leftJoin('part_companies as pc2', 'pc2.id', '=', 'bt.part_company_id')
                        ->join('sort_link_searches as sl', 'pc1.id', '=', 'sl.project_classify_id') //sort_link_searches 这个表仅仅是满足让查出的全部案卷，按照单位工程树排序的
                        ->where('sl.project_id', '=', $projectId)
                        ->whereNull('bt.deleted_at')
                        ->when($status, function ($query) use ($status) {
                            $query->where('bt.status', '=', $status);
                        })
                        ->when($titleKeyword, function ($query) use ($titleKeyword, $catalogTable, $partCompanyId) {
                            $likeSearch = "%{$titleKeyword}%";
                            $query->distinct()->where('bt.title', 'like', $likeSearch)
                                ->orWhereIn('bt.id', function (Builder $query) use ($likeSearch, $catalogTable, $partCompanyId) {
                                    $query->whereNull("$catalogTable.deleted_at")
                                        ->where('pc2.id', '=', $partCompanyId) //这个条件要加上，否则查到的数据可能会重复
                                        ->select('book_title_id')
                                        ->from($catalogTable)
                                        ->where("$catalogTable.title", 'like', $likeSearch);
                                });
                        })
                        ->when($archivesNo, function ($query) use ($archivesNo) {
                            $likeSearch2 = "%{$archivesNo}%";
                            $query->where('bt.archives_no', 'like', $likeSearch2);
                        })
                        ->select(
                            'bt.id', 'bt.is_need_check', 'bt.part_company_id', 'bt.project_classify_id',
                            'bt.serial_no', 'bt.archives_no', 'bt.title', 'bt.base_map',
                            'bt.blueprint', 'bt.word_material', 'bt.catalog_count', 'bt.page_num', 'bt.store_period',
                            'bt.remark', 'bt.cabin_no', 'bt.volume', 'bt.years', 'bt.secret_level',
                            'bt.mixed_no', 'bt.start_date', 'bt.end_date', 'bt.problem', 'bt.status',
                            'pc1.title as pc_name', 'pc2.title as part_company_name', 'sl.id as sort_link_id')
                );
            }
        }
        $unionQuery = $queries->shift(); //出列一张表作为union的开始
        $queries->each(function ($item) use ($unionQuery) { //循环剩下的表添加union
            $unionQuery->unionAll($item);
        });

        if (empty($unionQuery)) return [];
        /**
         * with(new BaseModel())  new BaseModel()换成别的也可以目前不清楚如何使用的
         * 设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
         */
        $list = with(new BaseModel())->setTable('union_book_title')
            ->from(DB::raw("({$unionQuery->toSql()}) as union_book_title")) //添加临时表
            ->mergeBindings($unionQuery)
            ->orderBy('sort_link_id')
            ->orderBy('serial_no')
            ->paginate($perPage);

        if ($list->isEmpty()) return [];

        $list = $list->toArray();
        $page = request()->input('page', 1);
        foreach ($list['data'] as $key => &$val) { //格式化起止日期格式为 20220910~20220910
            $val['number']         = ($page - 1) * $perPage + $key + 1;
            $val['title']          = htmlspecialchars_decode($val['title']);
            $val['start_end_date'] = format_date($val['start_date'], $val['end_date']);
            $val['data_count']     = $val['catalog_count'];
            $pdfPageInfo           = BookTitleService::getPdfPageNum($val['part_company_id'], $val['project_classify_id'], $val['id']);
            $val['pdf_page_count'] = $pdfPageInfo['pdf_page_count'];
            $val['pdf_count']      = $pdfPageInfo['pdf_count'];
        }
        $list['total_page_num']  = $totalPageNum;
        $list['total_blueprint'] = $totalBlueprint;

        return $this->removeNoUsedPageParams($list);
    }

    /**
     * 获取多个数据表的案卷数据
     */
    public function getUnionBookTitleList($tables, $perPage, $titleKeyword, $archivesNo = null, $fileNumber = null, $status = 0)
    {
        $queries        = collect(); //定义一个集合
        $partCompanyId  = request()->input('part_company_id');
        $totalPageNum   = 0;
        $totalBlueprint = 0;
        $tables         = array_unique($tables);
        foreach ($tables as $table) {
            if (Schema::hasTable($table)) {
                $tableParam   = CommonService::getTableParams($table);
                $catalogTable = 'book_catalogs_' . $tableParam['id1'] . '_' . $tableParam['id2'];
                /**
                 * 构造一个基础查询语句
                 */
                $sumBaseQuery = DB::table($table)
                    ->whereNull('deleted_at')
                    ->when($titleKeyword, function ($query) use ($table, $titleKeyword, $catalogTable) {
                        $likeSearch = "%{$titleKeyword}%";
                        $query->whereIn("$table.id", function (Builder $query) use ($catalogTable, $likeSearch) {
                            $query->select('book_title_id')->from($catalogTable)->where("$catalogTable.title", 'like', $likeSearch);
                        });
                    })
                    ->when($archivesNo, function ($query) use ($archivesNo, $table) {
                        $likeSearch2 = "%{$archivesNo}%";
                        $query->where("$table.archives_no", 'like', $likeSearch2);
                    })
                    ->when($fileNumber, function ($query) use ($table, $fileNumber, $catalogTable) {
                        $likeSearch3 = "%{$fileNumber}%";
                        $query->whereIn("$table.id", function (Builder $query) use ($catalogTable, $likeSearch3) {
                            $query->select('book_title_id')->from($catalogTable)->where("$catalogTable.file_number", 'like', $likeSearch3);
                        });
                    });
                /**
                 * 计算总页次
                 */
                $totalPageNum += $sumBaseQuery->sum('page_num');
                /**
                 * 计算总蓝图数
                 */
                $totalBlueprint += $sumBaseQuery->sum('blueprint');
                /**
                 * 获取数据列表
                 */
                $queries->push( //分表查询 已通过
                    DB::table($table)->from("$table as bt")
                        ->leftJoin('project_classifies as pc1', 'pc1.id', '=', 'bt.project_classify_id')
                        ->leftJoin('part_companies as pc2', 'pc2.id', '=', 'bt.part_company_id')
                        ->join('sort_links as sl', 'pc1.id', '=', 'sl.project_classify_id') //sort_links这个表仅仅是满足让查出的全部案卷，按照单位工程树排序的
                        ->where('sl.part_company_id', '=', $partCompanyId)
                        ->whereNull('bt.deleted_at')
                        ->when($status, function ($query) use ($status) {
                            $query->where('bt.status', '=', $status);
                        })
                        ->when($titleKeyword, function ($query) use ($titleKeyword, $catalogTable, $partCompanyId) {
                            $likeSearch = "%{$titleKeyword}%";
                            $query->distinct()->where('bt.title', 'like', $likeSearch)
                                ->orWhereIn('bt.id', function (Builder $query) use ($likeSearch, $catalogTable, $partCompanyId) {
                                    $query->whereNull("$catalogTable.deleted_at")
                                        ->where('sl.part_company_id', '=', $partCompanyId) //这个条件要加上，否则查到的数据可能会重复
                                        ->select('book_title_id')
                                        ->from($catalogTable)
                                        ->where("$catalogTable.title", 'like', $likeSearch);
                                });
                        })
                        ->when($archivesNo, function ($query) use ($archivesNo) {
                            $likeSearch2 = "%{$archivesNo}%";
                            $query->where('bt.archives_no', 'like', $likeSearch2);
                        })
                        ->when($fileNumber, function ($query) use ($fileNumber, $catalogTable, $partCompanyId) {
                            $likeSearch3 = "%{$fileNumber}%";
                            $query->whereIn('bt.id', function (Builder $query) use ($likeSearch3, $catalogTable, $partCompanyId) {
                                    $query->whereNull("$catalogTable.deleted_at")
                                        ->where('sl.part_company_id', '=', $partCompanyId) //这个条件要加上，否则查到的数据可能会重复
                                        ->select('book_title_id')
                                        ->from($catalogTable)
                                        ->where("$catalogTable.file_number", 'like', $likeSearch3);
                                });
                        })
                        ->select(
                            'bt.id', 'bt.is_need_check', 'bt.part_company_id', 'bt.project_classify_id',
                            'bt.serial_no', 'bt.archives_no', 'bt.title', 'bt.base_map',
                            'bt.blueprint', 'bt.word_material', 'bt.catalog_count', 'bt.page_num', 'bt.store_period',
                            'bt.remark', 'bt.cabin_no', 'bt.volume', 'bt.years', 'bt.secret_level',
                            'bt.mixed_no', 'bt.start_date', 'bt.end_date', 'bt.problem', 'bt.status',
                            'pc1.title as pc_name', 'pc2.title as part_company_name', 'sl.id as sort_link_id')
                );
            }
        }
        $unionQuery = $queries->shift(); //出列一张表作为union的开始
        $queries->each(function ($item) use ($unionQuery) { //循环剩下的表添加union
            $unionQuery->unionAll($item);
        });

        if (empty($unionQuery)) return [];
        /**
         * with(new BaseModel())  new BaseModel()换成别的也可以目前不清楚如何使用的
         * 设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
         */
        $list = with(new BaseModel())->setTable('union_book_title')
            ->from(DB::raw("({$unionQuery->toSql()}) as union_book_title")) //添加临时表
            ->mergeBindings($unionQuery)
            ->orderBy('sort_link_id')
            ->orderBy('serial_no')
            ->paginate($perPage);

        if ($list->isEmpty()) return [];

        $list = $list->toArray();
        $page = request()->input('page', 1);
        foreach ($list['data'] as $key => &$val) { //格式化起止日期格式为 20220910~20220910
            $val['number']         = ($page - 1) * $perPage + $key + 1;
            $val['title']          = htmlspecialchars_decode($val['title']);
            $val['start_end_date'] = format_date($val['start_date'], $val['end_date']);
            $val['data_count']     = $val['catalog_count'];
            $pdfPageInfo           = BookTitleService::getPdfPageNum($val['part_company_id'], $val['project_classify_id'], $val['id']);
            $val['pdf_page_count'] = $pdfPageInfo['pdf_page_count'];
            $val['pdf_count']      = $pdfPageInfo['pdf_count'];
        }
        $list['total_page_num']  = $totalPageNum;
        $list['total_blueprint'] = $totalBlueprint;

        return $this->removeNoUsedPageParams($list);
    }

    /**
     * 检索阶段卷内
     * can_look : 1 借阅未到期，可看PDF; 0不可看  ->from("book_catalogs_{$partCompanyId} as b")
     */
    public function getBookCatalogs($partCompanyId, $projectClassifyId, $bookTitleId)
    {
        $bookCatalogsInstance = $this->getBookCatalogInstance($partCompanyId, $projectClassifyId);
        $list                 = $bookCatalogsInstance
            ->where('book_title_id', $bookTitleId)
            ->get();
        if ($list->isEmpty()) return [];
        $list            = $list->makeHidden(['created_at', 'updated_at', 'deleted_at']);
        $canLookContents = $this->canLookContents();
        foreach ($list as &$val) {
            $val['number']   = $val['serial_no'];
            $val['c_date']   = date('Ymd', strtotime($val['c_date']));
            $val['can_look'] = 0;
            foreach ($canLookContents as $val2) {
                $ids = explode(',', $val2['ids']);
                if ($val2['part_company_id'] == $partCompanyId) {
                    if ($val2['type'] == 2 && in_array($val['id'], $ids)) { //如果是卷内，指定卷内可看
                        $val['can_look'] = 1;
                    }
                    if ($val2['type'] == 1 && in_array($bookTitleId, $ids)) { //如果当前所在案卷，属于借阅可看未到期，整个卷内PDF都可看
                        $val['can_look'] = 1;
                    }
                }
            }
        }

        return $list;
    }

    /**
     * 判断当前用户是否可以查看PDF, 注意过期时间
     * part_company_id 用来确定是哪个表
     * Array
     * (
     * [0] => Array
     * (
     * [type] => 1 //案卷
     * [part_company_id] => 1
     * [ids] => 101,102,103
     * )
     *
     * [1] => Array
     * (
     * [type] => 1
     * [part_company_id] => 1
     * [ids] => 101,102
     * )
     *
     * [2] => Array
     * (
     * [type] => 2 //卷内
     * [part_company_id] => 2
     * [ids] => 101,102
     * )
     * )
     */
    public function canLookContents()
    {
        $userId   = user_id() ?: 11;
        $redisKey = 'canLookBookTitles' . $userId;
        redis()->del($redisKey);
        if ($canLookContents = redis()->get($redisKey)) {
            $canLookContents = json_decode($canLookContents, true);
        } else {
            $list = ApprovalBorrow::query() //案卷
            ->where('creator_id', $userId)
                ->where('status', 1)
                ->where('expire_time', '>', now())
                ->select('type', 'part_company_id', 'ids', 'status')
                ->get();

            if ($list->isEmpty()) return [];

            $list            = $list->makeHidden(['status', 'status_text']);
            $canLookContents = $list->toArray();

            redis()->setex($redisKey, 60, json_encode($canLookContents));
        }

        return $canLookContents;
    }

}
